package com.jsyxdl.k3cloud.excel;

import cn.hutool.extra.spring.SpringUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.hustcad.plm.pdm.partbom.vo.PartDetailsVO;
import com.hustcad.plm.pdm.user.service.TyplmPrincipalService;
import com.ty.basic.entity.PrincipalEntity;
import com.ty.basic.utils.TyResultUtils;
import com.ty.security.util.TyJsonDataUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.FileSystemResource;
import org.springframework.core.io.Resource;
import org.springframework.util.StringUtils;

import java.io.*;
import java.util.List;
import java.util.Map;

public class ProductionChangeOrderUtils {
    private static final Logger log = LoggerFactory.getLogger(ProductionChangeOrderUtils.class);

    private Workbook templateWorkbook;

    /**
     * 根据配置路径获取Excel模板输入流
     *
     * @return 模板文件的输入流
     * @throws IOException 当文件找不到或读取失败时抛出
     */
    public void getExcelTemplate() throws IOException {
        String templatePath = new String(
                SpringUtil.getProperty("excel.template.path.ProductionChangeOrder").getBytes("ISO-8859-1"), "UTF-8");

        if (!StringUtils.hasText(templatePath)) {
            throw new IllegalArgumentException("Excel模板路径未配置(excel.template.path.ProcurementBOM)");
        }

        Resource resource = templatePath.startsWith("classpath:")
                ? new ClassPathResource(templatePath.substring("classpath:".length()))
                : new FileSystemResource(templatePath);

        try (InputStream is = resource.getInputStream();
             // 使用临时文件避免流问题
             InputStream tempIs = new BufferedInputStream(is)) {

            // 验证文件头
            tempIs.mark(100);
            byte[] header = new byte[4];
            tempIs.read(header);
            tempIs.reset();

            if (!(header[0] == 0x50 && header[1] == 0x4B && header[2] == 0x03 && header[3] == 0x04)) {
                throw new IOException("文件不是有效的Excel文件(缺少PK头)");
            }

            // 使用WorkbookFactory自动检测格式
            this.templateWorkbook = WorkbookFactory.create(tempIs);
            log.info("成功加载 Excel 模板文件");
        } catch (Exception e) {
            log.error("加载 Excel 模板文件失败 - 路径: {}, 大小: {}",
                      resource.getURI(),
                      resource.contentLength(),
                      e);
            throw e;
        }
    }


    /**
     * 量产物料变更描述说明（通知）
     */
    public void ProductionChangeOrderDescription() throws IOException {
        int count = 30;
        //信息页
        Sheet noticeSheet = templateWorkbook.getSheet("量产物料变更描述说明（通知）");

        Row row6 = noticeSheet.getRow(6);
        Cell cell = row6.getCell(0);
        Row srow6 = noticeSheet.createRow(count + 6);
        copyRow(row6, srow6);

        Font font = templateWorkbook.createFont();
        font.setUnderline(Font.U_SINGLE);
        font.setFontName("Arial");

        Row row7 = noticeSheet.getRow(7);
        cell = row7.getCell(0);

        Row srow7 = noticeSheet.createRow(count + 7);
        copyRow(row7, srow7, 3, font);


        Row row8 = noticeSheet.getRow(8);
        cell = row8.getCell(0);
        Row srow8 = noticeSheet.createRow(count + 8);
        copyRow(row8, srow8);

        Row row9 = noticeSheet.getRow(9);
        cell = row9.getCell(0);
        Row srow9 = noticeSheet.createRow(count + 9);
        copyRow(row9, srow9);

        Row row10 = noticeSheet.getRow(10);
        cell = row10.getCell(0);
        Row srow10 = noticeSheet.createRow(count + 10);
        copyRow(row10, srow10);

        // 如果是单行合并（跨列），直接复制
        CellRangeAddress newMergedRegion = new CellRangeAddress(
                count + 10, // 新起始行
                count + 10, // 新结束行
                0, // 原起始列
                7  // 原结束列
        );
        noticeSheet.addMergedRegion(newMergedRegion);


        // 如果是单行合并（跨列），直接复制
        newMergedRegion = new CellRangeAddress(
                count + 9, // 新起始行
                count + 9, // 新结束行
                0, // 原起始列
                7  // 原结束列
        );
        noticeSheet.addMergedRegion(newMergedRegion);


        // 如果是单行合并（跨列），直接复制
        newMergedRegion = new CellRangeAddress(
                count + 8, // 新起始行
                count + 8, // 新结束行
                0, // 原起始列
                1  // 原结束列
        );
        noticeSheet.addMergedRegion(newMergedRegion);


        // 如果是单行合并（跨列），直接复制
        newMergedRegion = new CellRangeAddress(
                count + 8, // 新起始行
                count + 8, // 新结束行
                2, // 原起始列
                5  // 原结束列
        );
        noticeSheet.addMergedRegion(newMergedRegion);

        // 如果是单行合并（跨列），直接复制
        newMergedRegion = new CellRangeAddress(
                count + 6, // 新起始行
                count + 6, // 新结束行
                1, // 原起始列
                2  // 原结束列
        );
        noticeSheet.addMergedRegion(newMergedRegion);
        // 如果是单行合并（跨列），直接复制
        newMergedRegion = new CellRangeAddress(
                count + 6, // 新起始行
                count + 6, // 新结束行
                3, // 原起始列
                4  // 原结束列
        );
        noticeSheet.addMergedRegion(newMergedRegion);
        // 如果是单行合并（跨列），直接复制
        newMergedRegion = new CellRangeAddress(
                count + 6, // 新起始行
                count + 6, // 新结束行
                6, // 原起始列
                7  // 原结束列
        );
        noticeSheet.addMergedRegion(newMergedRegion);

        // 如果是单行合并（跨列），直接复制
        newMergedRegion = new CellRangeAddress(
                count + 7, // 新起始行
                count + 7, // 新结束行
                0, // 原起始列
                1  // 原结束列
        );
        noticeSheet.addMergedRegion(newMergedRegion);

        // 如果是单行合并（跨列），直接复制
        newMergedRegion = new CellRangeAddress(
                count + 7, // 新起始行
                count + 7, // 新结束行
                2, // 原起始列
                7  // 原结束列
        );
        noticeSheet.addMergedRegion(newMergedRegion);

        noticeSheet.removeRow(row6);
        noticeSheet.removeRow(row7);
        noticeSheet.removeRow(row8);
        noticeSheet.removeRow(row9);
        noticeSheet.removeRow(row10);

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        templateWorkbook.write(outputStream);
        templateWorkbook.close();

        File outFile = new File("D:\\1.xlsx");

        FileOutputStream out = new FileOutputStream(outFile);

        out.write(outputStream.toByteArray());
        out.close();


    }

    /**
     * 量产物料变更管理单
     */
    public void ProductionChangeOrderManagement(String formData) throws IOException {


        JSONObject formDataJson = JSONObject.parseObject(formData);

        //信息页
        Sheet noticeSheet = templateWorkbook.getSheet("量产变更管理单");
        //表头第1行
        JSONArray selectuser63843 = formDataJson.getJSONArray("selectuser63843");
        if (selectuser63843 != null && selectuser63843.size() > 0) {
            setCellValue(noticeSheet, 2, 1, selectuser63843.getJSONObject(0).getString("fullname"));
        }
        setCellValue(noticeSheet, 2, 4, formDataJson.getString("input44882"));
        setCellValue(noticeSheet, 2, 7, formDataJson.getString("date61102"));

        //表头第2行
        setCellValue(noticeSheet, 3, 1, formDataJson.getString("input33695"));
        setCellValue(noticeSheet, 3, 4, formDataJson.getString("input85029"));
        setCellValue(noticeSheet, 3, 7, formDataJson.getString("input50507"));

        //表头第3行
        int radio75548 = formDataJson.getIntValue("radio75548");
        String types[] = new String[]{"顾客变更", "内部变更-设计需求", "内部变更-制程需求", "供应商变更", "其它"};
        String yes = "☑";
        String not = "□";
        StringBuilder radio75548Str = new StringBuilder();
        for (int i = 0; i < types.length; i++) {
            if (radio75548 == i + 1) {
                radio75548Str.append(yes);
                radio75548Str.append(types[i]);
                radio75548Str.append("      ");
            } else {
                radio75548Str.append(not);
                radio75548Str.append(types[i]);
                radio75548Str.append(" ");
            }
        }
        setCellValue(noticeSheet, 4, 1, radio75548Str.toString());

        //表头第4行
        setCellValue(noticeSheet, 5, 1, formDataJson.getString("textarea67623"));

        //表头第5行
        setCellValue(noticeSheet, 6, 1, formDataJson.getString("textarea23579"));

        //表头第6行
        setCellValue(noticeSheet, 7, 2, formDataJson.getString("textarea62379"));

        //表头第7行
        setCellValue(noticeSheet, 8, 2, formDataJson.getString("textarea57281"));


        //选择是否处
        //表头第10行
        setCellValue(noticeSheet, 11, 3, formDataJson.getString("textarea55186"));
        setCellValue(noticeSheet, 11, 6, formDataJson.getString("date79871"));
        String radio51017 = formDataJson.getString("radio51017");
        setCellValueByYesOrNot(noticeSheet, 11, 1, radio51017);


        JSONArray selectuser38843 = formDataJson.getJSONArray("selectuser38843");
        if (selectuser38843 != null && selectuser38843.size() > 0) {
            setCellValue(noticeSheet, 11, 8, selectuser38843.getJSONObject(0).getString("fullname"));
        }

        //表头第11行
        setCellValue(noticeSheet, 12, 3, formDataJson.getString("textarea6843"));
        setCellValue(noticeSheet, 12, 6, formDataJson.getString("date25082"));

        String radio104991 = formDataJson.getString("radio104991");
        setCellValueByYesOrNot(noticeSheet, 12, 1, radio104991);

        JSONArray selectuser27719 = formDataJson.getJSONArray("selectuser27719");
        if (selectuser27719 != null && selectuser27719.size() > 0) {
            setCellValue(noticeSheet, 12, 8, selectuser27719.getJSONObject(0).getString("fullname"));
        }
        //表头第12行
        setCellValue(noticeSheet, 13, 3, formDataJson.getString("textarea100127"));
        setCellValue(noticeSheet, 13, 6, formDataJson.getString("date49708"));

        String radio82979 = formDataJson.getString("radio82979");
        setCellValueByYesOrNot(noticeSheet, 13, 1, radio82979);

        JSONArray selectuser62706 = formDataJson.getJSONArray("selectuser62706");
        if (selectuser62706 != null && selectuser62706.size() > 0) {
            setCellValue(noticeSheet, 13, 8, selectuser62706.getJSONObject(0).getString("fullname"));
        }
        //表头第13行
        setCellValue(noticeSheet, 14, 3, formDataJson.getString("textarea33780"));
        setCellValue(noticeSheet, 14, 6, formDataJson.getString("date102352"));

        String radio95299 = formDataJson.getString("radio95299");
        setCellValueByYesOrNot(noticeSheet, 14, 1, radio95299);

        JSONArray selectuser58896 = formDataJson.getJSONArray("selectuser58896");
        if (selectuser58896 != null && selectuser58896.size() > 0) {
            setCellValue(noticeSheet, 14, 8, selectuser58896.getJSONObject(0).getString("fullname"));
        }
        //表头第14行
        setCellValue(noticeSheet, 15, 3, formDataJson.getString("textarea45180"));
        setCellValue(noticeSheet, 15, 6, formDataJson.getString("date73462"));

        String radio82462 = formDataJson.getString("radio82462");
        setCellValueByYesOrNot(noticeSheet, 15, 1, radio82462);


        JSONArray selectuser44933 = formDataJson.getJSONArray("selectuser44933");
        if (selectuser44933 != null && selectuser44933.size() > 0) {
            setCellValue(noticeSheet, 15, 8, selectuser44933.getJSONObject(0).getString("fullname"));
        }
        //表头第15行
        setCellValue(noticeSheet, 16, 3, formDataJson.getString("textarea59590"));
        setCellValue(noticeSheet, 16, 6, formDataJson.getString("date107347"));

        String radio37211 = formDataJson.getString("radio37211");
        setCellValueByYesOrNot(noticeSheet, 16, 1, radio37211);

        JSONArray selectuser108008 = formDataJson.getJSONArray("selectuser108008");
        if (selectuser108008 != null && selectuser108008.size() > 0) {
            setCellValue(noticeSheet, 16, 8, selectuser108008.getJSONObject(0).getString("fullname"));
        }
        //表头第16行
        setCellValue(noticeSheet, 17, 3, formDataJson.getString("textarea44920"));
        setCellValue(noticeSheet, 17, 6, formDataJson.getString("date10845"));

        String radio76284 = formDataJson.getString("radio76284");
        setCellValueByYesOrNot(noticeSheet, 17, 1, radio76284);

        JSONArray selectuser107601 = formDataJson.getJSONArray("selectuser107601");
        if (selectuser107601 != null && selectuser107601.size() > 0) {
            setCellValue(noticeSheet, 17, 8, selectuser107601.getJSONObject(0).getString("fullname"));
        }


        //表头第18行
        JSONArray checkbox99868 = formDataJson.getJSONArray("checkbox99868");
        if (checkbox99868 != null && checkbox99868.size() > 0) {
            setCellValueByCheckBox(noticeSheet, 19, 1, "原材料库", (String) checkbox99868.get(0));
        }
        setCellValue(noticeSheet, 19, 2, formDataJson.getString("input20285"));
        setCellValue(noticeSheet, 19, 3, formDataJson.getString("textarea35780"));
        setCellValue(noticeSheet, 19, 6, formDataJson.getString("date86575"));
        JSONArray selectuser25201 = formDataJson.getJSONArray("selectuser25201");
        if (selectuser25201 != null && selectuser25201.size() > 0) {
            setCellValue(noticeSheet, 19, 8, selectuser25201.getJSONObject(0).getString("fullname"));
        }


        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        templateWorkbook.write(outputStream);
        templateWorkbook.close();

        File outFile = new File("D:\\1.xlsx");

        FileOutputStream out = new FileOutputStream(outFile);

        out.write(outputStream.toByteArray());
        out.close();


    }

    // 辅助类，保存单元格引用和样式信息
    private static class CellStyleInfo {
        final CellReference cellReference;
        final CellStyle cellStyle;

        CellStyleInfo(CellReference cellReference, CellStyle cellStyle) {
            this.cellReference = cellReference;
            this.cellStyle = cellStyle;
        }
    }

    /**
     * 复制源行到目标行
     *
     * @param sourceRow 源行
     * @param targetRow 目标行
     */
    public static void copyRow(Row sourceRow, Row targetRow) {
        // 设置行高
        targetRow.setHeight(sourceRow.getHeight());

        // 复制每个单元格
        for (Cell sourceCell : sourceRow) {
            Cell targetCell = targetRow.createCell(sourceCell.getColumnIndex());

            // 复制单元格样式
            CellStyle newStyle = targetRow.getSheet().getWorkbook().createCellStyle();
            newStyle.cloneStyleFrom(sourceCell.getCellStyle());
            targetCell.setCellStyle(newStyle);

            // 复制单元格内容
            switch (sourceCell.getCellType()) {
                case STRING:
                    targetCell.setCellValue(sourceCell.getStringCellValue());
                    break;
                case NUMERIC:
                    targetCell.setCellValue(sourceCell.getNumericCellValue());
                    break;
                case BOOLEAN:
                    targetCell.setCellValue(sourceCell.getBooleanCellValue());
                    break;
                case FORMULA:
                    targetCell.setCellFormula(sourceCell.getCellFormula());
                    break;
                case BLANK:
                    targetCell.setBlank();
                    break;
                default:
                    targetCell.setCellValue(sourceCell.getStringCellValue());
            }
        }
    }

    /**
     * 复制源行到目标行，设置下划线
     *
     * @param sourceRow 源行
     * @param targetRow 目标行
     */
    public static void copyRow(Row sourceRow, Row targetRow, int index, Font font) {
        // 设置行高
        targetRow.setHeight(sourceRow.getHeight());

        // 复制每个单元格
        for (Cell sourceCell : sourceRow) {
            Cell targetCell = targetRow.createCell(sourceCell.getColumnIndex());

            // 复制单元格样式
            CellStyle newStyle = targetRow.getSheet().getWorkbook().createCellStyle();
            newStyle.cloneStyleFrom(sourceCell.getCellStyle());
            if (sourceCell.getColumnIndex() == index) {
                newStyle.setFont(font);
            }
            targetCell.setCellStyle(newStyle);


            // 复制单元格内容
            switch (sourceCell.getCellType()) {
                case STRING:
                    targetCell.setCellValue(sourceCell.getStringCellValue());
                    break;
                case NUMERIC:
                    targetCell.setCellValue(sourceCell.getNumericCellValue());
                    break;
                case BOOLEAN:
                    targetCell.setCellValue(sourceCell.getBooleanCellValue());
                    break;
                case FORMULA:
                    targetCell.setCellFormula(sourceCell.getCellFormula());
                    break;
                case BLANK:
                    targetCell.setBlank();
                    break;
                default:
                    targetCell.setCellValue(sourceCell.getStringCellValue());
            }
        }
    }

    /**
     * 复制合并单元格区域
     *
     * @param sheet
     * @param sourceRowIndex
     * @param targetRowIndex
     */
    private static void copyMergedRegions(Sheet sheet, int sourceRowIndex, int targetRowIndex) {
        // 获取所有合并区域
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();

        for (CellRangeAddress mergedRegion : mergedRegions) {
            // 检查合并区域是否包含源行
            if (mergedRegion.getFirstRow() <= sourceRowIndex &&
                    mergedRegion.getLastRow() >= sourceRowIndex) {

                // 创建新的合并区域（调整行号）
                CellRangeAddress newMergedRegion = new CellRangeAddress(
                        targetRowIndex, // 新起始行
                        targetRowIndex + (mergedRegion.getLastRow() - mergedRegion.getFirstRow()), // 新结束行
                        mergedRegion.getFirstColumn(), // 原起始列
                        mergedRegion.getLastColumn()   // 原结束列
                );

                // 添加到Sheet
                sheet.addMergedRegion(newMergedRegion);
            }
        }
    }

    /**
     * 根据字段名，到模板中找到引用表格，前提是模板表格中对表格进行了名称定义
     * 与字段名称相符的引用表格将作为表头，程序将从下一行开始写数据
     *
     * @param workbook 工作簿
     * @param strName 字段名称
     * @return 起始行引用
     */
    private CellReference findStartRow(Workbook workbook, String strName) {
        CellReference result = null;
        Name name = workbook.getName(strName);

        // 解析引用位置
        if (name != null) {
            // 获取名称引用的公式
            String refersToFormula = name.getRefersToFormula();
            log.info("引用公式: " + refersToFormula);
            try {
                // 处理单元格区域引用
                AreaReference areaRef = new AreaReference(refersToFormula, workbook.getSpreadsheetVersion());

                // 获取工作表名称
                String sheetName = name.getSheetName() != null ?
                        name.getSheetName() : "工作簿级别";
                log.info("所在工作表: " + sheetName);

                // 获取起始和结束单元格位置
                CellReference firstCell = areaRef.getFirstCell();
                CellReference lastCell = areaRef.getLastCell();

                log.info("起始位置: " + firstCell.formatAsString());
                log.info("结束位置: " + lastCell.formatAsString());

                result = firstCell;
            } catch (IllegalArgumentException e) {
                log.info("无法解析的位置: " + refersToFormula);
                return result;
            }
        }
        return result;
    }

    /**
     * 创建表头
     */
    private static void createSheetHeader(Sheet sheet) {
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("物料标识");
        headerRow.createCell(1).setCellValue("材料类型");
        headerRow.createCell(2).setCellValue("差异类型");
        headerRow.createCell(3).setCellValue("原数量");
        headerRow.createCell(4).setCellValue("新数量");
        headerRow.createCell(5).setCellValue("数量差值");
    }

    /**
     * 填充数据到 Sheet 页
     */
    private static void fillSheetData(Sheet sheet, List<Map<String, Object>> results) {
        int rowNum = 1;
        //        for (PartUsageLinkDiffResultDTO result : results) {
        //            Row row = sheet.createRow(rowNum++);
        //            row.createCell(0).setCellValue(result.getIdentifierDisplayName());
        //            row.createCell(1).setCellValue(result.getMaterialType());
        //            row.createCell(2).setCellValue(result.getDiffType());
        //            row.createCell(3).setCellValue(result.getOldAmount());
        //            row.createCell(4).setCellValue(result.getNewAmount());
        //            row.createCell(5).setCellValue(result.getAmountDiff());
        //        }

        // 调整列宽
        for (int i = 0; i < 5; i++) {
            sheet.autoSizeColumn(i);
        }
    }

    /**
     * 设置cell值
     *
     * @param sheet
     * @param rows
     * @param columns
     * @param value
     */
    private static void setCellValue(Sheet sheet, int rows, int columns, Object value) {
        Row row = sheet.getRow(rows);
        Cell cell = row.getCell(columns);

        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else {
        }
    }

    /**
     * 设置是或否
     *
     * @param sheet
     * @param rows
     * @param columns
     * @param value
     */

    private static void setCellValueByYesOrNot(Sheet sheet, int rows, int columns, String value) {
        String yes = "☑";
        String not = "□";
        StringBuilder stringBuilder = new StringBuilder();
        if ("1".equals(value)) {
            stringBuilder.append(yes);
            stringBuilder.append("是");
            stringBuilder.append("     ");
            stringBuilder.append(not);
            stringBuilder.append("否");
        } else if ("0".equals(value)) {
            stringBuilder.append(not);
            stringBuilder.append("是");
            stringBuilder.append("     ");
            stringBuilder.append(yes);
            stringBuilder.append("否");
        } else {
            stringBuilder.append(not);
            stringBuilder.append("是");
            stringBuilder.append("     ");
            stringBuilder.append(not);
            stringBuilder.append("否");
        }
        setCellValue(sheet, rows, columns, stringBuilder.toString());

    }

    /**
     * 内容前打勾或叉
     *
     * @param sheet
     * @param rows
     * @param columns
     * @param label
     * @param value
     */
    private static void setCellValueByCheckBox(Sheet sheet, int rows, int columns, String label, String value) {
        String yes = "☑";
        String not = "□";
        StringBuilder stringBuilder = new StringBuilder();
        if ("1".equals(value)) {
            stringBuilder.append(yes);
            stringBuilder.append(label);
        } else if ("0".equals(value)) {
            stringBuilder.append(not);
            stringBuilder.append(label);
        } else {
            stringBuilder.append(not);
            stringBuilder.append(label);
        }
        setCellValue(sheet, rows, columns, stringBuilder.toString());

    }

}

